За первое полугодие 2022 года падение рынка электроинструмента по сравнению с первым полугодием 2021 года составило 25%. В 2023 году ожидается продложение снижения рынка. В сявзи с этим необходимо подготовить предложения по развитию производства электроинструмента, а также повышение прибыльности изделий.
Целью проекта является подготовка рекомендаций для повышения прибыли направления электроинструмента
Импортируем библиотеки для работы с датафреймами и средств визуализации.
import pandas as pd
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
Откроем данные ABC-анализа (СМиП) и рентабельности (ФЭУ) электроинструмента и выведем на экран первые 5 строк.
abc = pd.read_excel(r'C:\Users\Геннадий Иовченко\Desktop\ABC\ABC.xlsx')
profit = pd.read_excel(r'C:\Users\Геннадий Иовченко\Desktop\ABC\profit.xlsx')
display(abc.head(), profit.head())
| № п/п | Год освоения | Наименование | Количество | Доля 9 мес, % | |
|---|---|---|---|---|---|
| 0 | 3 | 2007 | Б1-30 | 5717 | 0.045917 |
| 1 | 6 | 2013 | Б2-30 | 3869 | 0.031075 |
| 2 | 32 | 2019 | Б2-30 МАСТЕР | 710 | 0.005702 |
| 3 | 7 | 2014 | Б3-40 | 3843 | 0.030866 |
| 4 | 24 | 2019 | Б4-70 | 1306 | 0.010489 |
| Название | Рентабельность | |
|---|---|---|
| 0 | Б1-30 | 7.718157 |
| 1 | Б2-30 | 8.475530 |
| 2 | Б2-30 МАСТЕР | 11.512256 |
| 3 | Б3-40 | 23.588806 |
| 4 | Б4-70 | 12.719378 |
Для удобства работы переименуем столбцы в обоих датафреймах.
abc = abc.rename(
columns={
'№ п/п': 'number',
'Год освоения': 'year',
'Наименование': 'name',
'Количество': 'amount',
'Доля 9 мес, %': 'part'
}
).drop('number', axis=1)
profit = profit.rename(
columns={
'Название': 'name',
'Рентабельность': 'profit'
}
)
Переведём столбец с годом освоения в целочисленный формат.
abc['year'] = abc['year'].astype(int)
Переведём проценты в обоих датафреймах к единому виду.
abc['part'] = abc['part'] * 100
После того, как мы "причесали" наши данные, объединим их в единый датафрейм и отсортируем их по названию.
df = abc.merge(profit, on = 'name', how = 'inner').sort_values(by='name')
Добавим столбец с серией электроинструмента и типом электроинструмента.
df['series'] = 'Professional'
df.loc[abc['name'].str.contains('МАСТЕР'), 'series'] = 'Master'
df['type'] = 'бороздодел'
df.loc[df['name'].str.contains('МД'), 'type'] = 'миксер'
df.loc[df['name'].str.contains('МС'), 'type'] = 'дрель'
df.loc[df['name'].str.contains('МФ'), 'type'] = 'фреза'
df.loc[df['name'].str.contains('МШУ'), 'type'] = 'болгарка'
df.loc[df['name'].str.contains('П'), 'type'] = 'перфоратор'
df.loc[df['name'].str.contains('ПД'), 'type'] = 'пила'
df.loc[df['name'].str.contains('ПМ'), 'type'] = 'лобзик'
df.loc[df['name'].str.contains('ШВ'), 'type'] = 'шуруповёрт'
Приведём данные в удобный вид и округлим проценты. И посмотрим, что в итоге у нас получилось.
df = df[['year', 'name', 'type', 'series', 'amount', 'part', 'profit']]
df[['part', 'profit']] = round(df[['part', 'profit']], 2)
df.head()
| year | name | type | series | amount | part | profit | |
|---|---|---|---|---|---|---|---|
| 0 | 2007 | Б1-30 | бороздодел | Professional | 5717 | 4.59 | 7.72 |
| 1 | 2013 | Б2-30 | бороздодел | Professional | 3869 | 3.11 | 8.48 |
| 2 | 2019 | Б2-30 МАСТЕР | бороздодел | Master | 710 | 0.57 | 11.51 |
| 3 | 2014 | Б3-40 | бороздодел | Professional | 3843 | 3.09 | 23.59 |
| 4 | 2019 | Б4-70 | бороздодел | Professional | 1306 | 1.05 | 12.72 |
В итоге у нас получилась таблица с изделием, годом его освоения, типом изделия, серией, количеством проданных изделий, долей в портфеле заказа и рентабельностью.
fig = px.scatter(df,
y='part', x='profit', color='name', width=900, height=800,
labels={
'part': 'Доля продаж (%)',
'profit': 'Рентабельность (%)',
'type': 'Тип изделия',
'name': 'Изделие'
},
title='Продуктовая матрица')
fig.add_hline(y=0, line_dash='dash')
fig.add_vline(x=0, line_dash='dash')
fig.update_layout(yaxis_title='Доля в портфеле заказов',
xaxis_title='Рентабельность изделия')
fig.show()
Построим предыдущую матрицу с выделением не по конкретным изделиям, а по типам.
fig = px.scatter(df,
y='part', x='profit', color='type', width=900, height=800,
labels={
'part': 'Доля продаж (%)',
'profit': 'Рентабельность (%)',
'type': 'Тип изделия',
'name': 'Изделие',
'year': 'Год'
},
title='Продуктовая матрица по типу изделия')
fig.add_hline(y=0, line_dash='dash')
fig.add_vline(x=0, line_dash='dash')
fig.update_layout(yaxis_title='Доля в портфеле заказов',
xaxis_title='Рентабельность изделия')
fig.show()
Построим ту же матрицу рассеяния с цветовым выделением годов освоения.
fig = px.scatter(df,
y='part', x='profit', color='year', width=900, height=800,
labels={
'part': 'Доля продаж (%)',
'profit': 'Рентабельность (%)',
'type': 'Тип изделия',
'name': 'Изделие',
'year': 'Год'
},
title='Продуктовая матрица по годам')
fig.add_hline(y=0, line_dash='dash')
fig.add_vline(x=0, line_dash='dash')
fig.update_layout(yaxis_title='Доля в портфеле заказов',
xaxis_title='Рентабельность изделия')
fig.show()
Чтобы было корректное понимание ценности продукта, необходимо ввести новую метрику value (англ. ценность), которая будет определяться как произведение спроса на рентабельность. Если на изделие высокий спрос и оно приносит много денег, то ценность такого изделия будет высокая, если один из показателей будет около нуля (не популярный продукт или не приносящий прибыль), то его ценность так же будет нулевая. Однако, при отрицательной рентабельности высокий спрос будет оказывать медвежью услугу - чем больше убыточных изделий продадим, тем в больший минус будем себя загонять.
Ценность измеряется в условных единицах, которая прямо не несёт физического смысла, однако весьма полезно при сравнении двух или более изделий. И для определения безубыточности при сравнения с 0.
Выведем топ-10 ценных и топ-10 вредных изделий.
df['value'] = round(df['part'] * df['profit'], 2)
display(df.sort_values(by='value', ascending=False).head(10), df.sort_values(by='value', ascending=False).tail(10))
| year | name | type | series | amount | part | profit | value | |
|---|---|---|---|---|---|---|---|---|
| 6 | 2004 | МД1-11Э | миксер | Professional | 39173 | 31.46 | 15.35 | 482.91 |
| 18 | 1999 | МФ3-1100Э | фреза | Professional | 6988 | 5.61 | 13.81 | 77.47 |
| 3 | 2014 | Б3-40 | бороздодел | Professional | 3843 | 3.09 | 23.59 | 72.89 |
| 9 | 2014 | МД3-12Э | миксер | Professional | 3440 | 2.76 | 17.88 | 49.35 |
| 7 | 2011 | МД1-11Э МАСТЕР | миксер | Professional | 3050 | 2.45 | 16.85 | 41.28 |
| 0 | 2007 | Б1-30 | бороздодел | Professional | 5717 | 4.59 | 7.72 | 35.43 |
| 1 | 2013 | Б2-30 | бороздодел | Professional | 3869 | 3.11 | 8.48 | 26.37 |
| 4 | 2019 | Б4-70 | бороздодел | Professional | 1306 | 1.05 | 12.72 | 13.36 |
| 17 | 2009 | МФ2-620Э | фреза | Master | 1406 | 1.13 | 5.98 | 6.76 |
| 2 | 2019 | Б2-30 МАСТЕР | бороздодел | Master | 710 | 0.57 | 11.51 | 6.56 |
| year | name | type | series | amount | part | profit | value | |
|---|---|---|---|---|---|---|---|---|
| 19 | 2018 | МФ4-1100Э | фреза | Master | 2138 | 1.72 | -5.73 | -9.86 |
| 30 | 2003 | МШУ2-9-125Э | болгарка | Professional | 3374 | 2.71 | -3.99 | -10.81 |
| 40 | 2008 | ПМ3-650Э | лобзик | Master | 1712 | 1.38 | -9.54 | -13.17 |
| 29 | 2002 | МШУ2-9-125 | болгарка | Professional | 3183 | 2.56 | -5.48 | -14.03 |
| 43 | 2011 | ПМ5-750Э МАСТЕР | лобзик | Professional | 1773 | 1.42 | -11.03 | -15.66 |
| 12 | 2010 | МСУ10-13-РЭ | дрель | Master | 3940 | 3.16 | -5.52 | -17.44 |
| 31 | 2006 | МШУ3-11-150 | болгарка | Master | 2427 | 1.95 | -9.33 | -18.19 |
| 41 | 2005 | ПМ4-700Э | лобзик | Master | 3043 | 2.44 | -7.74 | -18.89 |
| 39 | 2003 | ПМ3-600Э | лобзик | Professional | 3235 | 2.60 | -9.21 | -23.95 |
| 42 | 2006 | ПМ5-720Э | лобзик | Professional | 4292 | 3.45 | -8.19 | -28.26 |
В дальнейшем для наглядности при визуализации исключаем МД1-11Э.
Построим горизонтальную столбчатую диаграмму для всех изделий.
fig = px.bar(df.query('value<100').sort_values(by='value'),
y='name',
x='value',
#color='type',
orientation='h',
width=900,
height=860,
title='Ценность изделия',
labels={
'value': 'Ценность',
'name': 'Изделие'
}
)
fig.show()
Переформатируем исходные столбчатые диграммы с разбиением по типу изделий
fig = px.bar(df.query('value<100').sort_values(by='value'),
y='name',
x='value',
color='type',
orientation='h',
width=900,
height=860,
title='Ценность изделия по типу',
labels={
'value': 'Ценность',
'name': 'Изделие',
'type': 'Тип изделия'
}
)
fig.show()
Дополнительно выведем отдельно убыточные и прибыльные изделия.
df_low_value = df.query('value<0').sort_values(by='value')
df_high_value = df.query('0<value<100').sort_values(by='value')
fig = make_subplots(rows=2, cols=1)
fig.append_trace(go.Bar(
y=df_low_value['name'],
x=df_low_value['value'],
orientation='h',
name='Убыточные',
), 1, 1)
fig.append_trace(go.Bar(
y=df_high_value['name'],
x=df_high_value['value'],
orientation='h',
name='Прибыльные',
), 2, 1)
fig.update_layout(
title='Вклад убыточных и прибыльных изделий',
height=1100
)
Сохраним итоговый датафрейм в формате xlsx для возможности дальнейшей работы.
df.to_excel(r'C:\Users\Геннадий Иовченко\Desktop\ABC\product_matrix.xlsx')